#coding:utf-8
##ALTER TABLE `table1` ADD `AAAA` VARCHAR( 10 ) NOT NULL ;插入字段AAAA
#PRAGMA table_info(table_name)
import sqlite3
import cPickle as pickle

class sqlite_handle():
    def __init__(self):
        self.cxn=sqlite3.connect(r'g:\myapp\tb_data')
        self.cur=self.cxn.cursor()
        
    def create_table(self,table):
        try:
            self.cur.execute('CREATE TABLE %s (id INTERGER PRIMARY KEY,name CHAR(8),age CHAR(8)' %table)
            self.cxn.commit()
        except:
            pass
    def table_heads(self,table):
        '''返回表字段名'''
        result = self.cur.execute('PRAGMA table_info(%s)' %table)
        return [i[1] for i in result]
    def add_head(self,table,head):
        '''插入字段'''
        if head not in self.table_heads(table):
            sql='ALTER TABLE `%s` ADD `%s`' %(table,head)
            self.cur.execute(sql)
            self.cxn.commit()
        else:
            print '%s already in %s' %(head,table)
    def del_head(self,table,head):
        '''删除字段'''
        #BEGIN TRANSACTION;
        #CREATE TEMPORARY TABLE t1_backup(a,b);
        #INSERT INTO t1_backup SELECT a,b FROM t1;
        #DROP TABLE t1;
        #CREATE TABLE t1(a,b);
        #INSERT INTO t1 SELECT a,b FROM t1_backup;
        #DROP TABLE t1_backup;
        #COMMIT;
        heads = self.table_heads(table)
        if head not in heads:
            print '%s already in %s' %(head,table)
        else:
            heads.remove(head)
            sql = '''
                CREATE TEMPORARY TABLE %(table)s_back (%(heads_str)s);
                INSERT INTO %(table)s_back SELECT %(heads_str)s FROM %(table)s;
                DROP TABLE %(table)s;
                CREATE TABLE %(table)s(%(heads_str)s);
                INSERT INTO %(table)s SELECT %(heads_str)s FROM %(table)s_back;
                DROP TABLE %(table)s_back;
                '''
            sql_query = sql %{'table':table,'heads':heads,'heads_str':','.join(heads)}
            self.cur.executescript(sql_query)
            self.cxn.commit()
            print '%s already move from %s' %(head,table)

if __name__ == '__main__':
    test = sqlite_handle()
    print test.table_heads('tb_data')
    test.add_head('tb_data', 'AAAAa')
    test.del_head('tb_data', 'AAAAa')
    print test.table_heads('tb_data')
    